package com.ncloud.billing.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.ncloud.billing.entity.ChnlDetailBill;

@Mapper
public interface ChnlDetailBillMapper{

	@Select("SELECT bill_id, total_id, parent_id,chnl_id, chnl_name, user_id, sale_id, sale_name, order_id, cust_id, cust_name, discnt_inst_id, discnt_id, discnt_name,"
			+ "prod_inst_id, prod_id, prod_name, cycle_id, fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_detailbill "
			+ "where parent_id = #{parent_id} ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="total_id",property="total_id"),@Result(column="parent_id",property="parent_id"),
		@Result(column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="user_id",property="user_id"),@Result(column="sale_id",property="sale_id"),
		@Result(column="sale_name",property="sale_name"),@Result(column="order_id",property="order_id"),
		@Result(column="cust_id",property="cust_id"),@Result(column="cust_name",property="cust_name"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="discnt_id",property="discnt_id"),
		@Result(column="discnt_name",property="discnt_name"),@Result(column="prod_inst_id",property="prod_inst_id"),
		@Result(column="prod_id",property="prod_id"),@Result(column="prod_name",property="prod_name"),
		@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlDetailBill> getChnlDetailBillByChnlID(Long parent_id);

	@Select("SELECT bill_id, total_id, parent_id,chnl_id, chnl_name, user_id, sale_id, sale_name, order_id, cust_id, cust_name, discnt_inst_id, discnt_id, discnt_name,"
			+ "prod_inst_id, prod_id, prod_name, cycle_id, fee, balance, bill_tag,date_format(create_time, '%Y-%m-%d %H:%i:%s' ) as create_time, create_user_id, "
			+ "date_format(update_time, '%Y-%m-%d %H:%i:%s' ) as update_time, update_user_id, remark FROM sys_chnl_detailbill "
			+ "where total_id = #{total_id} ")
	@Results({
		@Result(column="bill_id",property="bill_id"),@Result(column="total_id",property="total_id"),@Result(column="parent_id",property="parent_id"),
		@Result(column="chnl_id",property="chnl_id"),@Result(column="chnl_name",property="chnl_name"),
		@Result(column="user_id",property="user_id"),@Result(column="sale_id",property="sale_id"),
		@Result(column="sale_name",property="sale_name"),@Result(column="order_id",property="order_id"),
		@Result(column="cust_id",property="cust_id"),@Result(column="cust_name",property="cust_name"),
		@Result(column="discnt_inst_id",property="discnt_inst_id"),@Result(column="discnt_id",property="discnt_id"),
		@Result(column="discnt_name",property="discnt_name"),@Result(column="prod_inst_id",property="prod_inst_id"),
		@Result(column="prod_id",property="prod_id"),@Result(column="prod_name",property="prod_name"),
		@Result(column="cycle_id",property="cycle_id"),@Result(column="fee",property="fee"),
		@Result(column="balance",property="balance"),@Result(column="bill_tag",property="bill_tag"),
		@Result(column="create_time",property="create_time"),@Result(column="create_user_id",property="create_user_id"),
		@Result(column="update_time",property="update_time"),@Result(column="update_user_id",property="update_user_id"),
		@Result(column="remark",property="remark")})
	List<ChnlDetailBill> getChnlDetailBillByTotalID(Long total_id);
	
	@Insert(value = "INSERT INTO sys_chnl_detailbill (bill_id, total_id,parent_id, chnl_id, chnl_name, user_id, sale_id, sale_name, order_id, cust_id, cust_name, discnt_inst_id, discnt_id, discnt_name, prod_inst_id, prod_id, prod_name, cycle_id, fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark) "
									+ "values(#{bill_id},#{total_id},#{parent_id},#{chnl_id},#{chnl_name},#{user_id},#{sale_id},#{sale_name},#{order_id},#{cust_id},#{cust_name},#{discnt_inst_id},#{discnt_id},#{discnt_name},#{prod_inst_id},#{prod_id},#{prod_name},#{cycle_id},#{fee},#{balance},#{bill_tag},#{create_time},#{create_user_id},#{update_time},#{update_user_id},#{remark})")
    public int insertChnlDetailBill(ChnlDetailBill chnlDetailBill);
	
	/*@Insert(value = "INSERT INTO sys_chnl_bill (bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id,cycle_id,fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark)  "
			+ " SELECT bill_id, chnl_id, user_id, order_id, cust_id, discnt_inst_id,cycle_id,fee, balance, bill_tag, create_time, create_user_id, update_time, update_user_id, remark FROM sys_chnl_realbill where cycle_id = #{cycle_id}")
	public int insertChnlBillFromReal(Integer cycle_id);*/
	
	@Update(value = "UPDATE sys_chnl_detailbill SET balance = #{balance}, bill_tag = #{bill_tag}, update_time = #{update_time}, update_user_id = #{update_user_id} WHERE total_id = #{total_id} ")
    public int updateChnlDetailBill(ChnlDetailBill chnlDetailBill);
}
